#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@NAME          : sqlite.py
@TIME          : 2025/02/16 13:41:25
@AUTHOR        : chenlip
@VERSION       : 0.0.1
@DESCRIPTION   : sqlite数据库操作
                1- 创建数据库
                2- 创建表
                3- 插入数据
                4- 查询数据
                5- 更新数据
                6- 删除数据
                7- 关闭连接
'''
# --------------------------引用--------------------------
import os, sys, json
import sqlite3

# --------------------------引用--------------------------

class MediaSqliteAct():
    def __init__(self, dbfile:str = None):
        '''
            初始化sqlite操作对象, 参数是数据库文件路径，如果不存在则创建
            如果存在则打开
            :Args:
                - dbfile: 数据库文件路径，例如：d:/mysqlite.db
            :Return:
        '''
        if dbfile is None:
            raise ValueError("数据库文件路径不能为空")

        self.dbfile = dbfile

        try:
            # 尝试连接数据库，无论文件是否存在
            self.conn = sqlite3.connect(self.dbfile)
            self.cursor = self.conn.cursor()
            self.conn.text_factory = str
            self.cursor.execute('PRAGMA foreign_keys = ON')
            self.cursor.execute('PRAGMA encoding = "UTF-8"')

            # 如果文件不存在，则创建表并初始化数据
            if not os.path.exists(dbfile):
                self.create_table()
                # 初始化数据
        except Exception as e:
            self.conn.close()
            raise RuntimeError(f"初始化数据库失败: {e}")

    def __del__(self):
        '''
            确保在对象销毁时关闭数据库连接
        '''
        if hasattr(self, 'conn') and self.conn:
            self.conn.close()

    def _log_ex(self, message, exception):
        '''
            显示打印异常信息
        '''
        error_message = f"""
            {"#"*80}
            {message}
            {exception}
            {"#"*80}
            """
        print(error_message)
    def create_table(self):
        '''
            创建数据库表；
            表名：media_file_info
            结构：
                - id: 自增id
                - source: 来源，值：百度、阿里、minio、本地...
                - file_id: 原网盘文件id, 如：676233b22f4b615ce0cf4922b4086e74c227474f
                - cpath: 在云端网盘的路径，如：/2021/01/01/IMG_20210101_000001.jpg
                - lpath: 在本地的路径，如：d:/2021/01/01/IMG_20210101_000001.jpg
                - fname: 文件名，如：IMG_20210101_000001.jpg
                - ext: 扩展名，如：jpg
                - created_at: 创建时间 (云端创建时间)， 如：2021-01-01 00:00:01
                - updated_at: 修改时间 (云端修改时间)， 如：2021-01-01 00:00:01
                - local_created_at: 本地创建时间(原始文件创建时间)，如：2021-01-01 00:00:01
                - local_modified_at: 本地修改时间(原始文件修改时间)，如：2021-01-01 00:00:01
                - size: 文件大小, 如：237456
                - box_w: 原始文件宽, 如：1920
                - box_h: 原始文件高, 如：1080
                - watermark: 水印信息，json格式字符串；如："{"watermark":{"text":"watermark","position":"top-left","color":"#000000","opacity":0.5}}"
                - exif: exif信息，json格式字符串；如："{"exif":{"Make":"Canon","Model":"Canon EOS 5D Mark III","Orientation":1,"XResolution":72,"YResolution":72,"ResolutionUnit":2,"Software":"Canon EOS 5D Mark III","DateTime":"2021:01:01 00:00:01","YCbCrPositioning":2,"ExifVersion":"0230","ImageWidth":6000,"ImageLength":4000,"BitsPerSample":8,"Compression":6,"PhotometricInterpretation":2,"Orientation":1,"XResolution":72,"YResolution":72,"ResolutionUnit":2,"Software":"Canon EOS 5D MarkIII","DateTime":"2021:01:01 00:00
                - metadata: 元数据，json格式字符串；可以包含图片的理解文本和标签，如："{"metadata":{"labels":["label1","label2","label3"],"texts":["text1","text2","text3"]}}"
                - s3_urls: s3地址，json格式字符串；如："{"s3_urls":{"origin":"https://example.com/origin.jpg","thumbnail":"https://example.com/thumbnail.jpg","small":"https://example.com/small.jpg","medium":"https://example.com/medium.jpg","large":"https://example.com/large.jpg"}}"
                - status: 文件状态（0-入库；1-去水印；2-高清；3-扩图；4-缩略图；5-上传minio; 6-生成文章; N-禁用；D-删除）
                - note: 备注

            :Args:
                - var: 描述
            :Return:
        '''
        sql = '''
            CREATE TABLE IF NOT EXISTS media_file_info (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                source TEXT,
                file_id TEXT,
                cpath TEXT,
                lpath TEXT,
                fname TEXT,
                ext TEXT,
                created_at TEXT,
                updated_at TEXT,
                local_created_at TEXT,
                local_modified_at TEXT,
                size INTEGER,
                box_w INTEGER,
                box_h INTEGER,
                watermark TEXT,
                exif TEXT,
                metadata TEXT,
                s3_urls TEXT,
                status INTEGER,
                note TEXT
                )
        '''
        # 判断表是否存在，如果不存在则创建
        self.cursor.execute('SELECT count(*) FROM sqlite_master WHERE type="table" AND name="media_file_info"')
        if self.cursor.fetchone()[0] == 0:
            print('==== 数据表 media_file_info 不存在, 从新创建. =====')
            try:
                self.cursor.execute(sql)
                self.conn.commit()
            except Exception as e:
                self._log_ex("创建数据表 media_file_info 失败", e)
                self.conn.rollback()

    # create_table

    def insert_data(self, data: dict):
        '''
            插入数据
            :Args:
                - data: 数据字典, 格式：
                {
                    "source": "baidupan",
                    "file_id": "676233b22f4b615ce0cf4922b4086e74c227474f",
                    "cpath": "/", # 云端路径
                    "lpath": "/", # 本地路径
                    "fname": "IMG_20210101_000001.jpg",
                    "ext": "jpg",
                    "size": 123456,
                    "box":[], # width, height
                    "created_at": "2021-02-01 10:03:32", # 云端创建时间
                    "updated_at": "2021-02-01 10:03:32", # 云端更新时间
                    "local_created_at": "2021-01-01 11:03:32", # 本地创建时间
                    "local_modified_at": "2021-01-02 08:00:01", # 本地更新时间
                    "watermark": {}, # 水印信息
                    "exif": {}, # 图片的exif信息
                    "metadata": {}, # 图片的元数据，可以包含图片的理解文本和标签
                    "s3_urls": {}, # 图片的S3地址
                    "status": "1", # 文件状态
                    "note": "no" # 文件备注
                }
            :Return:
                - 插入成功返回id，否则返回0
        '''
        if not data:
            return
        
        # 转换水印信息
        watermark = data.get('watermark', {})
        if watermark:
            data["watermark"] = json.dumps(watermark)
        
        # 转换exif信息
        exif = data.get('exif', {})
        if exif:
            data['exif'] = json.dumps(exif)

        # 转换元数据信息
        metadata = data.get('metadata', {})
        if metadata:
            data['metadata'] = json.dumps(metadata)

        # 转换s3地址信息
        s3_urls = data.get('s3_urls', {})
        if s3_urls:
            data['s3_urls'] = json.dumps(s3_urls)

        # 转换data['box_w'] = data.get('box', [0, 0])[0]
        data['box_w'] = data.get('box', [0, 0])[0]
        data['box_h'] = data.get('box', [0, 0])[1]

        sql = '''
            INSERT INTO media_file_info (
                source, file_id, cpath, lpath, fname, ext, created_at, updated_at, local_created_at, local_modified_at, size, box_w, box_h, watermark, exif, metadata, s3_urls, status, note
                ) VALUES (
                :source, :file_id, :cpath, :lpath, :fname, :ext, :created_at, :updated_at,:local_created_at, :local_modified_at, :size, :box_w, :box_h, :watermark, :exif, :metadata, :s3_urls, :status, :note
                )
        '''
        try:
            # 插入数据
            self.cursor.execute(sql, data)
            self.conn.commit()
            # 获取最后插入的id
            last_id = self.cursor.lastrowid
        except Exception as e:
            self._log_ex(f"数据库插入语句 \n {sql} ,异常", e)
            self.conn.rollback()
            return 0

        return last_id
    
    def update_data(self, data: dict):
        '''
            更新数据
            :Args:
                - data: 数据字典, 格式：
                {
                    "id": 1, # 必须的
                    "source": "baidupan",
                    "file_id": "676233b22f4b615ce0cf4922b4086e74c227474f",
                    "cpath": "/", # 云端路径
                    "lpath": "/", # 本地路径
                    "fname": "IMG_20210101_000001.jpg",
                    "ext": "jpg",
                    "size": 123456,
                    "box":[], # width, height
                    "created_at": "2021-02-01 10:03:32", # 云端创建时间
                    "updated_at": "2021-02-01 10:03:32", # 云端更新时间
                    "local_created_at": "2021-01-01 11:03:32", # 本地创建时间
                    "local_modified_at": "2021-01-02 08:00:01", # 本地更新时间
                    "watermark": {}, # 水印信息
                    "exif": {}, # 图片的exif信息
                    "metadata": {}, # 图片的元数据，可以包含图片的理解文本和标签
                    "s3_urls": {}, # 图片的S3地址
                    "status": "1", # 文件状态
                    "note": "no" # 文件备注
                }
            :Return:
                - 更新成功返回True，否则返回False
        '''
        if not data:
            return
        
        # 转换水印信息
        watermark = data.get('watermark')
        if watermark:
            data["watermark"] = json.dumps(watermark)
        
        # 转换exif信息
        exif = data.get('exif')
        if exif:
            data['exif'] = json.dumps(exif)
        
        # 转换元数据信息
        metadata = data.get('metadata')
        if metadata:
            data['metadata'] = json.dumps(metadata)
        
        # 转换s3地址信息
        s3_urls = data.get('s3_urls')
        if s3_urls:
            data['s3_urls'] = json.dumps(s3_urls)

        # 转换data['box_w'] = data.get('box', [0, 0])[0]
        data['box_w'] = data.get('box', [0, 0])[0]
        data['box_h'] = data.get('box', [0, 0])[1]

        # 更加data中的key 组合更新语句
        _sql = ''
        for key in data.keys():
            if key == 'id':
                continue
            
            _sql += key + ' = :' + key + ', '

            # 如果是最后一个key，则去掉逗号
            if key == list(data.keys())[-1]:
                _sql = _sql[:-2]
            
        # 更新数据的SQL
        sql = "UPDATE media_file_info SET " + _sql + " WHERE id = :id"
        try:
            self.cursor.execute(sql, data)
            self.conn.commit()
            return True
        except Exception as e:
            self._log_ex(f"数据库执行更新语句 \n {sql} ,异常", e)
            self.conn.rollback()
            return False
        
    # 将查询结果格式化为List(dict)这种结构
    def format_data(self, res: list):
        '''
            将查询结果格式化为List(dict)这种结构
            :Args:
                - res: 查询结果
            :Return:
                格式化后的结果
        '''
        if not res:
            return []
        
        # 获取字段名
        fields = [desc[0] for desc in self.cursor.description]
        
        # 将查询结果转换为字典列表
        data = [dict(zip(fields, row)) for row in res]
        
        # 将字典列表中的值转换为对应的数据类型
        for item in data:
            for key, value in item.items():
                if value is not None:
                    if key in ['watermark', 'exif', 'metadata', 's3_urls']:
                        item[key] = json.loads(value)
                    elif key in ['status','id','size','box_w','box_h']:
                        item[key] = int(value)
                    else:
                        item[key] = value

        return data
    
    def query_data(self, sql: str):
        '''
            根据输入的sql语句查询数据
            :Args:
                - sql: 查询语句
            :Return:
                查询结果, List(dict)
        '''
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        dataset = self.format_data(res)
        if not dataset:
            return []
        
        return dataset
    
    def del_data(self, id: int):
        '''
            根据输入的sql语句删除数据
            :Args:
                - sql: delete 语句
            :Return:
                删除结果
        '''
        sql = "DELETE FROM media_file_info WHERE id = " + str(id)
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            return True
        except Exception as e:
            self._log_ex(f"数据库执行删除语句 \n {sql} ,异常", e)
            self.conn.rollback()
            return False

    # 按日期查询媒体文件，并根据local_created_at字段进行排序
    def query_files_by_cdate(self, datestr: str) -> list:
        '''
            按创建日期查询媒体文件，并根据local_created_at字段进行排序
            local_created_at字段表示本地创建时间, 如：'2021-01-01 11:03:32'
            查询逻辑：
                将local_created_at字段转换为'YYYY-MM-DD', 然后进行比较；
            :Args:
                - date: 日期，格式为YYYY-MM-DD

            :Return:
                查询结果列表

        '''
        sql = "SELECT * FROM media_file_info WHERE local_created_at LIKE '%" + datestr + "%' ORDER BY local_created_at DESC"
        return self.query_data(sql)
    
    # 按文件大小查询媒体文件，并根据size字段进行排序
    def query_files_by_size(self, size: int) -> list:
        '''
            按文件大小查询媒体文件，并根据size字段进行排序
            size字段表示文件大小，单位为字节
            查询逻辑：将size字段转换为KB，然后进行比较；
            :Args:
                - size: 文件大小，单位为KB, 如100k/500k等
            :Return:
                查询结果列表
        '''
        sql = "SELECT * FROM media_file_info WHERE size > " + str(size * 1024) + " ORDER BY size DESC"
        return self.query_data(sql)
    
    def query_files_by_box(self, box: list, gt: list) -> list:
        '''
            按文件宽/高查询媒体文件，并根据size字段进行排序
            box_w/box_h字段表示文件宽高，单位为像素
            查询逻辑：将size字段转换为KB，然后进行比较；
            :Args:
                - box: 文件宽高，如[100, 100]/[500, 500]等
                - gt: 是否大于等于，如[True, True]/[False, False]等,含义为宽大于等于100px, 高大于等于100px

            :Return:
                查询结果列表

            :示例：
                query_files_by_box([100, 100], [True, True]), 表示查询宽大于等于100px, 高大于等于100px的媒体文件
                query_files_by_box([500, 500], [False, False])， 表示查询宽小于500px, 高小于500px的媒体文件
                query_files_by_box([100, 100], [True, False]), 表示查询宽大于等于100px, 高小于100px的媒体文件
        '''
        sql = "SELECT * FROM media_file_info WHERE "
        for i in range(len(box)):
            if gt[i]:
                sql += "box_" + ["w", "h"][i] + " >= " + str(box[i]) + " AND "
            else:
                sql += "box_" + ["w", "h"][i] + " <= " + str(box[i]) + " AND "
        
        sql = sql[:-4] + " ORDER BY size DESC"
        return self.query_data(sql)






